﻿Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.Odbc
Imports Entidad

Public Class NotaCreditoDatos

    Private Path As String = "C:\DATOS\PRUEBA\"
    Private Path1 As String = "C:\DATOS\CFG\"

    Private Function fechaSincronizacion(ByVal tipo As String) As String
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim adapter As SqlDataAdapter
        Dim ds1 As New DataSet
        Dim query As String = ""
        Dim fechaFormateada As String = ""
        If tipo.Equals("NotaCredito") Then
            query = "select SINC_Fecha from sincroniza where SINC_Tipo = 'NotaCredito'"
            adapter = New SqlDataAdapter(query, coneccion)
            adapter.Fill(ds1)
            Dim fecha As Date = ds1.Tables(0).Rows(0)("SINC_Fecha")
            fechaFormateada = fecha.ToString("MM/dd/yyyy")
        End If
        coneccion.Close()
        Return fechaFormateada
    End Function

    Function carpetaMensual() As String
        Dim sConn As String
        Dim mesanio As String = ""
        Dim mes As String = ""
        Dim anio As String = ""
        Dim sqlComm As OdbcDataAdapter

        Dim query As String = "SELECT meses,anhos FROM matrocfg where codcia = '01'"
        sConn = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(Me.Path1 + "MATROCFG.DBF") & ";"

        Using dbConn As New System.Data.Odbc.OdbcConnection(sConn)
            dbConn.Open()
            sqlComm = New OdbcDataAdapter(query, dbConn)
            Dim dsOc As New Data.DataSet()
            sqlComm.Fill(dsOc)
            mes = dsOc.Tables(0).Rows(0)("meses").ToString.Trim
            anio = dsOc.Tables(0).Rows(0)("anhos").ToString.Trim
            mesanio = anio.Substring(2) & mes
            dbConn.Close()
        End Using

        Return mesanio
    End Function

    Function obtenerNotasCreditoDBF() As DataSet
        Dim notas As New DataTable
        notas.Columns.Add("docto")
        notas.Columns.Add("docto2")
        notas.Columns.Add("fchpro")
        notas.Columns.Add("cliente")
        notas.Columns.Add("tdocrela")
        notas.Columns.Add("ndocrela")

        Dim detalleNotas As New DataSet

        Dim sConn As String = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(Me.Path + carpetaMensual() + "\ARINGDO2.DBF") & ";"
        Dim query As String = "select docto from ARINGDO2 where len(alltrim(item)) > 3 " &
                              "group by docto"

        Using dbConn As New System.Data.Odbc.OdbcConnection(sConn)
            Try
                dbConn.Open()
                Dim sqlComm As New OdbcDataAdapter
                sqlComm = New OdbcDataAdapter(query, dbConn)
                sqlComm.Fill(detalleNotas)
            Catch ex As Exception
                MsgBox(ex.Message)
            Finally
                dbConn.Close()
            End Try
        End Using

        Dim sConn1 As String = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(Me.Path + carpetaMensual() + "\ARINGDO1.DBF") & ";"

        For Each fila As DataRow In detalleNotas.Tables(0).Rows
            Using dbConn1 As New System.Data.Odbc.OdbcConnection(sConn1)
                Try
                    dbConn1.Open()

                    Dim docto As String = fila.Item(0).ToString.Trim

                    Dim query1 As String = "SELECT docto,docto2,fchpro,cliente,atransx,tdocrela,ndocrela from ARINGDO1 " &
                                           "WHERE tdoc = '4' AND talon = 0 AND " &
                                           "fchpro >= {" & fechaSincronizacion("NotaCredito") & "} AND " &
                                           "docto = '" & docto & "' AND cliente <> '00-000'"

                    Dim sqlComm1 As New OdbcDataAdapter
                    sqlComm1 = New OdbcDataAdapter(query1, dbConn1)
                    Dim notasDet As New DataSet
                    sqlComm1.Fill(notasDet)

                    If notasDet.Tables(0).Rows.Count <> 0 Then
                        Dim dr As DataRow = notas.NewRow
                        dr("docto") = notasDet.Tables(0).Rows(0)("docto").ToString.Trim
                        dr("docto2") = notasDet.Tables(0).Rows(0)("docto2").ToString.Trim
                        dr("fchpro") = notasDet.Tables(0).Rows(0)("fchpro").ToString.Trim
                        dr("cliente") = notasDet.Tables(0).Rows(0)("cliente").ToString.Trim
                        dr("tdocrela") = notasDet.Tables(0).Rows(0)("tdocrela").ToString.Trim
                        dr("ndocrela") = notasDet.Tables(0).Rows(0)("ndocrela").ToString.Trim

                        notas.Rows.Add(dr)
                        notas.AcceptChanges()
                    End If

                Catch ex As Exception
                    MsgBox(ex.Message)
                Finally
                    dbConn1.Close()
                End Try
            End Using
        Next

        Dim dataSet As DataSet = New DataSet
        dataSet.Tables.Add(notas)

        Return dataSet
    End Function

    Sub registrarNotaCredito(ByVal nc As NotaCredito)
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "INSERT INTO Documento_Cliente(CIA,SUC_Codigo, " &
                              "TDOC_Codigo,DCI_Codigo,DCI_Fecha,CLI_Codigo, " &
                              "VEN_Codigo,MON_Codigo,TDO_Codigo,CNV_Codigo, " &
                              "FOP_Codigo,AGE_Codigo,DCI_TotalBruto,DCI_TotalNeto, " &
                              "DCI_IGV,DCI_Total,DCI_UltModificacion,DCI_Observaciones, " &
                              "DCI_ObsCredito,DCI_DireccionEntrega,DCI_ExcedeDscto, " &
                              "DCI_Estado) " &
                              "VALUES(@cia,@suc,@tipo,@dciCodigo,null,@cliCodigo,null, " &
                              "null,null,null,null,null,null,null,null,null,null,null, " &
                              "null,null,null,@est)"

        Dim comando As New SqlCommand
        With comando
            .Connection = coneccion
            .CommandText = query
            .Parameters.AddWithValue("@cia", nc.CIA)
            .Parameters.AddWithValue("@suc", nc.SUC)
            .Parameters.AddWithValue("@tipo", nc.TIPO)
            .Parameters.AddWithValue("@dciCodigo", nc.DCI_Codigo)
            .Parameters.AddWithValue("@clicodigo", nc.CLI_Codigo)
            .Parameters.AddWithValue("@est", nc.EST)
            .ExecuteNonQuery()
        End With
        coneccion.Close()
    End Sub

    Function obtenerDocumento(ByVal codDoc As String, ByVal codCIA As String, ByVal codSUC As String, ByVal codTip As String) As NotaCredito
        Dim nc As NotaCredito = Nothing

        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT TDOC_Codigo, DCI_Codigo, CLI_Codigo FROM Documento_Cliente " &
                              "WHERE DCI_Codigo = '" & codDoc & "' AND CIA = '" & codCIA & "' " &
                              "AND SUC_Codigo = '" & codSUC & "' AND TDOC_Codigo = '" & codTip & "'"

        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        If ds.Tables(0).Rows.Count <> 0 Then
            nc = New NotaCredito(Nothing, Nothing, Nothing, ds.Tables(0).Rows(0)("DCI_Codigo").ToString.Trim, ds.Tables(0).Rows(0)("CLI_Codigo").ToString.Trim, Nothing)
        End If

        Return nc
    End Function

    Sub anularNotaCredito(ByVal dciCodigo As String)
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "Update Documento_Cliente set EST_Codigo = '17' " &
                              "where DCI_Codigo = @dcicodigo"
        Dim comando As New SqlCommand
        With comando
            .Connection = coneccion
            .CommandText = query
            .Parameters.AddWithValue("@dcicodigo", dciCodigo)
            .ExecuteNonQuery()
        End With
        coneccion.Close()
    End Sub

    Function verificarRelacion(ByVal codDocumento As String) As Boolean
        Dim valor As Boolean = False
        Dim sConn As String = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(Me.Path + carpetaMensual() + "\ARINGDO1.DBF") & ";"
        Using dbConn As New System.Data.Odbc.OdbcConnection(sConn)
            Try
                dbConn.Open()

                Dim query1 As String = "select tdocrela,ndocrela from ARINGDO1 " &
                                       "where docto = '" & codDocumento & "'"

                Dim sqlComm1 As New OdbcDataAdapter
                sqlComm1 = New OdbcDataAdapter(query1, dbConn)
                Dim ds As New DataSet
                sqlComm1.Fill(ds)

                If ds.Tables(0).Rows(0)("tdocrela").ToString.Trim <> "" And ds.Tables(0).Rows(0)("ndocrela").ToString.Trim <> "" Then
                    valor = True
                End If

            Catch ex As Exception
                MsgBox(ex.Message)
            Finally
                dbConn.Close()
            End Try
        End Using

        Return valor
    End Function

    Function verificarMotivoAnulacion(ByVal codMot As String) As String
        Dim sqlComm As OdbcDataAdapter
        Dim sConn As String
        Dim dsOc As New Data.DataSet()

        Dim query As String = "SELECT abrev1 FROM INFOGEN WHERE code+subcode='0401' and item = '" & codMot & "'"

        sConn = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(Me.Path + "INFOGEN.dbf") & ";"

        Using dbConn As New System.Data.Odbc.OdbcConnection(sConn)
            Try
                dbConn.Open()
                sqlComm = New OdbcDataAdapter(query, dbConn)
                sqlComm.Fill(dsOc)
            Catch ex As Exception
                MsgBox(ex.Message)
            Finally
                dbConn.Close()
            End Try
        End Using

        Return dsOc.Tables(0).Rows(0)("abrev1").ToString.Trim
    End Function

    Function obtenerCodigoAnulacion(ByVal codDoc As String) As String
        Dim sqlComm As OdbcDataAdapter
        Dim sConn As String
        Dim dsOc As New Data.DataSet()

        Dim query As String = "SELECT banco FROM ARINGDO1 WHERE docto = '" & codDoc & "'"

        sConn = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(Me.Path + carpetaMensual() + "\ARINGDO1.DBF") & ";"

        Using dbConn As New System.Data.Odbc.OdbcConnection(sConn)
            Try
                dbConn.Open()
                sqlComm = New OdbcDataAdapter(query, dbConn)
                sqlComm.Fill(dsOc)
            Catch ex As Exception
                MsgBox(ex.Message)
            Finally
                dbConn.Close()
            End Try
        End Using

        Return dsOc.Tables(0).Rows(0)("banco").ToString.Trim
    End Function

    Sub actualizaFechaSincronizacion()
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "Update sincroniza set SINC_Fecha = getDate() where SINC_Tipo = 'NotaCredito'"
        Dim comando As New SqlCommand
        With comando
            .Connection = coneccion
            .CommandText = query
            .ExecuteNonQuery()
        End With
        coneccion.Close()
    End Sub

    Sub anularNotaCredito(ByVal tdoc As String, ByVal codDoc As String)
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "UPDATE Documento_Cliente " &
                              "SET EST_Codigo = '17' " &
                              "WHERE TDOC_Codigo = @tdoc' AND DCI_Codigo = @doc"
        Dim comando As New SqlCommand
        With comando
            .Connection = coneccion
            .CommandText = query
            .Parameters.AddWithValue("@tdoc", tdoc)
            .Parameters.AddWithValue("@doc", codDoc)
            .ExecuteNonQuery()
        End With
        coneccion.Close()
    End Sub

End Class
